package com.jiepos.mpos.biz.transaction.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;
import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.dao.LogInsCommProfitSumDao;
import com.jiepos.mpos.common.entity.App_Log_Ins_Month_Profit;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.DateUtil;
import com.jiepos.mpos.core.util.ExcelUtil;
import com.jiepos.mpos.core.util.StringUtils;

/**
 * 机构佣金报表业务类
 * 
 * @ProjectName: [mpos1]
 * @Package com.jiepos.mpos.biz.transaction.service
 * @author 肖志伟
 * @version 1.0
 * @since 2017年10月10日 下午2:06:29
 * @history（历次修订内容、修订人、修订时间等） @UpdateRemark [说明本次修改内容]
 * @Copyright (c) 2017 江苏杰博实信息技术有限公司
 */
@Service
public class LogInsCommProfitSumService extends BaseService {

	@Autowired
	private LogInsCommProfitSumDao logInsCommProfitSumDao;

	/**
	 * 列表页查询数据
	 * 
	 * @param request
	 * @param pagination
	 * @param appLogInsMonthProfit
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param ins_cd
	 * @param ins_name
	 * @return
	 */
	@SuppressWarnings({"rawtypes"})
	public Map getLogStlInsSumListJson(HttpServletRequest request, Pagination pagination,
			App_Log_Ins_Month_Profit appLogInsMonthProfit, String stl_dt_start, String stl_dt_end, String ins_cd,
			String ins_name) {
		JSONObject common = getSqlAndPrams(stl_dt_start, stl_dt_end, ins_cd, ins_name);
		pagination.setQueryString(common.getString("sql"));
		pagination = super.getInsAuthSql(request, pagination, "t1", null);
		pagination.queryString = pagination.queryString + " order by t1.stl_dt desc";
		log.debug("getTransactionLogList queryString: " + pagination.queryString);
		return logInsCommProfitSumDao.getPaginationMap(pagination, "t1", common.getObject("params", List.class));

	}

	/**
	 * 导出数据
	 * 
	 * @param fileName
	 * @param request
	 * @param response
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @param custom_name
	 * @param custom_phone
	 * @param ins_cd
	 * @param ins_name
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String executeSaveLogInsMonthProfitSum(String fileName, HttpServletRequest request, String stl_dt_start,
			String stl_dt_end, String ins_cd, String ins_name) {
		JSONObject common = getSqlAndPrams(stl_dt_start, stl_dt_end, ins_cd, ins_name);
		String sql = super.getInsAuthSql(request, common.getString("sql"), "t1", null);
		sql = sql + " order by t1.stl_dt desc";
		List<Map> resultList = logInsCommProfitSumDao.queryForList(sql, common.getObject("params", List.class));
		List<String> headList = new ArrayList<String>();
		headList.add("清算日期");
		headList.add("机构号");
		headList.add("机构名称");
		headList.add("业务名称");
		headList.add("业务参数名称");
		headList.add("交易笔数");
		headList.add("交易金额");
		headList.add("会员手续费");
		headList.add("会员结算手续费");
		headList.add("机构结算手续费");
		headList.add("机构毛利");
		headList.add("下级分润");
		return ExcelUtil.writeExcel(fileName, "机构分润报表", headList, resultList);
	}

	/**
	 * 会员清算交易汇总导出
	 * 
	 * @param request
	 * @param response
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @param custom_name
	 * @param custom_phone
	 * @param ins_cd
	 * @param ins_name
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public JSONObject getSqlAndPrams(String stl_dt_start, String stl_dt_end, String ins_cd, String ins_name) {
		StringBuffer sqlSb = new StringBuffer();
		List list = new ArrayList();
		sqlSb.append("SELECT TO_CHAR(TO_DATE(t1.stl_dt, 'YYYY-MM-DD'), 'yyyy-mm-dd') as stl_dt1, ");
		sqlSb.append("TO_CHAR(TO_DATE(t1.stl_dt, 'YYYY-MM-DD'), 'yyyy-mm-dd') as stl_dt, ");
		sqlSb.append("t3.ins_cd, ");
		sqlSb.append("t3.INS_CN_NM, ");
		sqlSb.append("t4.service_no||'-'||t4.service_name as service_name, ");
		sqlSb.append("t5.param_no||'-'||t5.param_name as service_param_name, ");
		sqlSb.append("t1.cnt, ");// 交易笔数
		sqlSb.append("to_char(t1.amt,'fm9999999990.00') as amt, ");// 交易金额
		sqlSb.append("to_char(t1.midfee,'fm9999999990.00') as midfee, "); // 会员手续费
		sqlSb.append("to_char(t1.custom_stl_fee,'fm9999999990.00') as custom_stl_fee, "); // 会员结算手续费
		sqlSb.append("to_char(t1.ins_stl_fee,'fm9999999990.00') as ins_stl_fee, "); // 机构结算手续费
		sqlSb.append("to_char(t1.profit_amt,'fm9999999990.00') as profit_amt, "); // 机构毛利
		sqlSb.append("to_char(t1.lower_profit_amt,'fm9999999990.00') as lower_profit_amt "); // 下级分润
		sqlSb.append("FROM ");
		sqlSb.append(Table.T_APP_LOG_INS_MONTH_PROFIT + " t1 ");
		sqlSb.append("LEFT JOIN ");
		sqlSb.append(Table.T_APP_INS_INF + " t3 ");
		sqlSb.append("on t1.ins_uuid = t3.ins_uuid ");
		sqlSb.append("LEFT JOIN ");
		sqlSb.append(Table.T_APP_SERVICE_INF + " t4 ");
		sqlSb.append("on t1.service_uuid = t4.service_uuid ");
		sqlSb.append("LEFT JOIN ");
		sqlSb.append(Table.T_APP_SERVICE_PARAM + " t5 ");
		sqlSb.append("on t1.service_param_uuid = t5.service_param_uuid ");
		sqlSb.append("where 1=1 ");
		if (!StringUtils.isBlank(stl_dt_start)) {
			Date date = DateUtil.strToDate(stl_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd")); 
		}else{
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(DateUtil.StringToDate(DateUtil.getPreDate()), "yyyyMMdd"));//默认显示日期的前一天的记录
		}
		if (!StringUtils.isBlank(stl_dt_end)) {
			Date date = DateUtil.strToDate(stl_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{ //无值默认今天
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(DateUtil.StringToDate(DateUtil.getPreDate()), "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(ins_cd)) {
			sqlSb.append(" and t3.ins_cd = ? ");
			list.add(ins_cd);
		}
		if (!StringUtils.isBlank(ins_name)) {
			sqlSb.append(" and t3.ins_cn_nm like ? ");
			list.add("%" + ins_name + "%");
		}
		JSONObject common = new JSONObject();
		common.put("sql", sqlSb.toString());
		common.put("params", list);
		return common;
	}

	/**
	 * 获取汇总数据
	 * @param request
	 * @param appLogInsMonthProfit
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param ins_cd
	 * @param ins_name
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map getLogInsCommProfitSumTotal(HttpServletRequest request, App_Log_Ins_Month_Profit appLogInsMonthProfit,
			String stl_dt_start, String stl_dt_end, String ins_cd, String ins_name) {
		StringBuffer sqlSb = new StringBuffer();
		List list = new ArrayList();
		sqlSb.append("SELECT  ");
		sqlSb.append("sum(t1.cnt) as count_cnt, ");// 交易笔数
		sqlSb.append("to_char(COALESCE(SUM(t1.amt),0),'fm9999999990.00') as sum_amt, ");// 交易金额
		sqlSb.append("to_char(COALESCE(SUM(t1.lower_profit_amt),0),'fm9999999990.00') as sum_lower_profit_amt, "); // 下级分润
		sqlSb.append("to_char(COALESCE(SUM(t1.midfee),0),'fm9999999990.00') as sum_midfee, "); // 会员手续费
		sqlSb.append("to_char(COALESCE(SUM(t1.profit_amt),0),'fm9999999990.00') as sum_profit_amt, "); // 机构分润
		sqlSb.append("to_char(COALESCE(SUM(t1.custom_stl_fee),0),'fm9999999990.00') as sum_custom_stl_fee, "); // 会员结算手续费
		sqlSb.append("to_char(COALESCE(SUM(t1.ins_stl_fee),0),'fm9999999990.00') as sum_ins_stl_fee "); //机构结算手续费
		sqlSb.append("FROM ");
		sqlSb.append(Table.T_APP_LOG_INS_MONTH_PROFIT + " t1 ");
		sqlSb.append("LEFT JOIN ");
		sqlSb.append(Table.T_APP_INS_INF + " t3 ");
		sqlSb.append("on t1.ins_uuid = t3.ins_uuid ");
		sqlSb.append("LEFT JOIN ");
		sqlSb.append(Table.T_APP_SERVICE_INF + " t4 ");
		sqlSb.append("on t1.service_uuid = t4.service_uuid ");
		sqlSb.append("LEFT JOIN ");
		sqlSb.append(Table.T_APP_SERVICE_PARAM + " t5 ");
		sqlSb.append("on t1.service_param_uuid = t5.service_param_uuid ");
		sqlSb.append("where 1=1 ");
		if (!StringUtils.isBlank(stl_dt_start)) {
			Date date = DateUtil.strToDate(stl_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd")); 
		}else{
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(DateUtil.StringToDate(DateUtil.getPreDate()), "yyyyMMdd"));//默认显示日期的前一天的记录
		}
		if (!StringUtils.isBlank(stl_dt_end)) {
			Date date = DateUtil.strToDate(stl_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{ //无值默认今天
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(DateUtil.StringToDate(DateUtil.getPreDate()), "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(ins_cd)) {
			sqlSb.append(" and t3.ins_cd = ? ");
			list.add(ins_cd);
		}
		if (!StringUtils.isBlank(ins_name)) {
			sqlSb.append(" and t3.ins_cn_nm like ? ");
			list.add("%" + ins_name + "%");
		}
		String sql = sqlSb.toString();
		sql = super.getInsAuthSql(request, sql, "t1", null);
		log.debug("getTransactionLogList queryString: " + sql);
		return (Map)logInsCommProfitSumDao.queryForList(sql, list).get(0);
	}
}
